//
//  YPKLocalDataManager.m
//  Yopark
//
//  Created by 王家骏 on 14/11/11.
//  Copyright (c) 2014年 developer. All rights reserved.
//

#import "YPKLocalDataManager.h"
#import "FMDatabase.h"
#import "FMDatabaseQueue.h"


@implementation YPKLocalDataManager
static YPKLocalDataManager *_instance;
+ (YPKLocalDataManager *)getInstance
{
    if (!_instance)
    {
        _instance = [[YPKLocalDataManager alloc]init];
    }
    return _instance;
}

- (void)createTable
{
    NSUserDefaults *userDefault = [NSUserDefaults standardUserDefaults];
    if ([userDefault boolForKey:@"isFirstOpenApp"]) {
        [userDefault setBool:NO forKey:@"isCreateDB"];
    }
    
    if (![userDefault boolForKey:@"isCreateDB"]) {
        
        /// 移动数据库
        NSFileManager *file_manager = [NSFileManager defaultManager];
        NSString *path = [[NSBundle mainBundle] pathForResource:@"YoparkDatabase" ofType:@"sqlite"];
        NSURL *url = [[NSURL alloc]initFileURLWithPath:path];
        NSData *db_data = [[NSData alloc]initWithContentsOfURL:url];
        NSString *path2 = [PATH_OF_DOCUMENT stringByAppendingPathComponent:@"YoparkDatabase.sqlite"];
        if([file_manager createFileAtPath:path2 contents:db_data attributes:nil]){
            [userDefault setBool:YES forKey:@"isCreateDB"];
            [userDefault synchronize];
            DLog(@"创建数据库成功");
            
        }
        else{
            DLog(@"创建数据库失败");
        }
        
        
    }
    
    NSString * doc = PATH_OF_DOCUMENT;
    NSString * path = [doc stringByAppendingPathComponent:@"YoparkDatabase.sqlite"];
    self.dbPath = path;
    NSLog(@"path = %@",path);
}
- (void)insertIntoHotHouseWithArray:(NSArray *)array
{
    FMDatabaseQueue *queue = [FMDatabaseQueue databaseQueueWithPath:self.dbPath];
    // 如果要支持事务
    [queue inTransaction:^(FMDatabase *db, BOOL *rollback) {
        if ([db open])
        {
            for (int i = 0; i < array.count; i ++)
            {
                
                NSString *houseId = array[i][@"house_id"];
                NSString *houseType = array[i][@"house_type"];
                NSString *title = array[i][@"title"];
                NSString *longPrice = array[i][@"long_price"];
                NSString *middlePrice = array[i][@"middle_price"];
                NSString *shortPrice = array[i][@"short_price"];
                NSString *price = array[i][@"price"];
                NSString *minNights = array[i][@"min_nights"];
                NSString *bedroomNum = array[i][@"bedroom_num"];
                NSString *roomNum = array[i][@"room_num"];
                NSString *washroomNum = array[i][@"washroom_num"];
                NSString *square = array[i][@"square"];
                NSString *personNum = array[i][@"person_num"];
                NSString *bedNum = array[i][@"bed_num"];
                NSString *districtId = array[i][@"district_id"];
                NSString *imgURLString = array[i][@"img_url"];
                NSString *landmarkName = array[i][@"landmark_name"];
                NSString *specialPriceStatus = array[i][@"special_price_status"];
                NSString *specialPrice = array[i][@"special_price"];
                
                NSString * sqlString = [NSString stringWithFormat:@"insert into hothouse (house_id, long_price, middle_price,  short_price, price, landmark_name, img_url, title, bedroom_num, room_num, washroom_num, square, person_num, bed_num, min_nights, district_id, house_type,special_price_status,special_price) values('%@','%@','%@','%@','%@','%@','%@','%@','%@','%@','%@','%@','%@','%@','%@','%@','%@','%@','%@')",houseId, longPrice, middlePrice, shortPrice, price,landmarkName, imgURLString, title ,  bedroomNum, roomNum, washroomNum, square, personNum, bedNum,minNights, districtId, houseType,specialPriceStatus,specialPrice];
                
                BOOL res = [db executeUpdate:sqlString];
                
                if (!res)
                {
                    DLog(@"error to insert hothouse");
                } else {
//                    DLog(@"succ to insert hothouse");
                }
            }
            
        }
    }];
    [queue close];
}
- (NSMutableArray *)queryHotHouseWithHouseType:(NSString *)houseType
{
    FMDatabase * db = [FMDatabase databaseWithPath:self.dbPath];
    if ([db open])
    {
        NSMutableArray *data = [[NSMutableArray alloc] init];
        NSString *sql = [NSString stringWithFormat:@"select * from hothouse where house_type = '%@'",houseType];
        FMResultSet * rs = [db executeQuery:sql];
        
        while ([rs next])
        {
            HomePageHouseModel *homePageHouseModel = [[HomePageHouseModel alloc] init];
            homePageHouseModel.houseId = [rs stringForColumn:@"house_id"];
            homePageHouseModel.title = [rs stringForColumn:@"title"];
            homePageHouseModel.price = [rs stringForColumn:@"price"];
            homePageHouseModel.longPrice = [rs stringForColumn:@"long_price"];
            homePageHouseModel.middlePrice = [rs stringForColumn:@"middle_price"];
            homePageHouseModel.shortPrice = [rs stringForColumn:@"short_price"];
            homePageHouseModel.minNights = [rs stringForColumn:@"min_nights"];
            homePageHouseModel.bedroomNum = [rs stringForColumn:@"bedroom_num"];
            homePageHouseModel.roomNum = [rs stringForColumn:@"room_num"];
            homePageHouseModel.washroomNum = [rs stringForColumn:@"washroom_num"];
            homePageHouseModel.square = [rs stringForColumn:@"square"];
            homePageHouseModel.personNum = [rs stringForColumn:@"person_num"];
            homePageHouseModel.bedNum = [rs stringForColumn:@"bed_num"];
            homePageHouseModel.districtId = [rs stringForColumn:@"district_id"];
            homePageHouseModel.imgURLString = [rs stringForColumn:@"img_url"];
            homePageHouseModel.houseType = [rs stringForColumn:@"house_type"];
            homePageHouseModel.landmarkName = [rs stringForColumn:@"landmark_name"];
            homePageHouseModel.specialPriceStatus = [[rs stringForColumn:@"special_price_status"] boolValue];
            homePageHouseModel.specialPrice = [rs stringForColumn:@"special_price"];
            
            [data addObject:homePageHouseModel];
        }
        [db close];
        
        
        return data;
    }
    return nil;
}
- (void)deleteHouseWithHouseType:(int)houseType
{
    FMDatabase * db = [FMDatabase databaseWithPath:self.dbPath];
    if ([db open]) {

        NSString *sql = [NSString stringWithFormat:@"delete from hothouse where house_type = '%d'",houseType];

        
        BOOL res = [db executeUpdate:sql];
        if (!res) {
            DLog(@"error to delete  hothouse");
        } else {
//            DLog(@"succ to delete  hothouse");
        }
        [db close];
    }
}

- (void)insertIntoMyInfo:(NSDictionary *)dic;

{
    FMDatabaseQueue *queue = [FMDatabaseQueue databaseQueueWithPath:self.dbPath];
    // 如果要支持事务
    [queue inTransaction:^(FMDatabase *db, BOOL *rollback) {
        if ([db open]) {
            NSString *user_id = dic[@"user_id"];
            NSString *username = dic[@"username"];
            NSString *phonenum = dic[@"phone"];
            NSString *email = dic[@"email"];
            NSString *level = dic[@"level"];
            NSString *password = dic[@"password"];
            NSString *accountnum = dic[@"accountnum"];
            NSString *sqlString = [NSString stringWithFormat:@"insert into person (user_id, username, phonenum, email, level, password, accountnum)values('%@','%@','%@','%@','%@','%@','%@')",user_id, username, phonenum, email, level, password, accountnum];
            BOOL res = [db executeUpdate:sqlString];
            if (!res)
            {
                DLog(@"error to insert person");
            } else {
                DLog(@"succ to insert person");
            }
        }

    }];
    [queue close];
}

- (void)updateIntoMyInfo:(NSDictionary *)dic
{
    FMDatabaseQueue *queue = [FMDatabaseQueue databaseQueueWithPath:self.dbPath];
    // 如果要支持事务
    [queue inTransaction:^(FMDatabase *db, BOOL *rollback) {
        if ([db open]) {
            NSString *user_id = dic[@"user_id"];
            NSArray *array = @[@"user_id",@"username",@"email",@"level",@"password",@"accountnum"];
            for (int i = 0; i < array.count; i++) {
                NSString *str = dic[array[i]];
                if (str) {
                NSString *sqlString = [NSString stringWithFormat:@"update person set '%@' = '%@'  where user_id ='%@'", array[i] , str, user_id];
                BOOL res = [db executeUpdate:sqlString];
                if (!res)
                {
                    DLog(@"error to update person");
                } else {
//                    DLog(@"succ to update person");
                }

                }

            }
            NSString *phonenum = [dic objectForKey:@"phone"];
            NSString *sqlString = [NSString stringWithFormat:@"update person set 'phonenum' = '%@'  where user_id ='%@'", phonenum , user_id];
            BOOL res = [db executeUpdate:sqlString];
            if (!res)
            {
                DLog(@"error to update person");
            } else {
                //                    DLog(@"succ to update person");
            }
        }
        
    }];
    [queue close];


}


//- (YPKMyKeyModel *)queryMyInfoWithUser_id:(NSString *)user_id
//{
//    FMDatabase * db = [FMDatabase databaseWithPath:self.dbPath];
//    if ([db open])
//    {
//            
//        NSString *sql = [NSString stringWithFormat:@"select * from person where user_id = '%@'",user_id];
//        FMResultSet * rs = [db executeQuery:sql];
//        YPKMyKeyModel *keyModel = [[YPKMyKeyModel alloc] init];
//        while ([rs next]) {
//            keyModel.username = [rs stringForColumn:@"username"];
//            keyModel.phonenum = [rs stringForColumn:@"phonenum"];
//            keyModel.email = [rs stringForColumn:@"email"];
//            keyModel.level = [rs stringForColumn:@"level"];
//            keyModel.password = [rs stringForColumn:@"password"];
//            keyModel.accountnum = [rs stringForColumn:@"accountnum"];
//            keyModel.user_id = [rs stringForColumn:@"user_id"];
//        }
//        
//        [db close];
//        return keyModel;
//    }
//    return nil;
//}

- (void)deleteMyInfoWithUserID:(NSString *)user_id
{
    FMDatabase * db = [FMDatabase databaseWithPath:self.dbPath];
    if ([db open]) {
        
        NSString *sql = [NSString stringWithFormat:@"delete from person where user_id = '%@'",user_id];

        BOOL res = [db executeUpdate:sql];
        if (!res) {
            DLog(@"error to delete  person");
        } else {
            DLog(@"succ to delete  hothouse");
        }
        [db close];
    }
}

- (void)insertRecordWithString:(NSString *)string
{
    FMDatabase * db = [FMDatabase databaseWithPath:self.dbPath];
    if ([db open]) {
        
        NSString *sql = [NSString stringWithFormat:@"insert into search_record(record)values('%@')",string];
        
        BOOL res = [db executeUpdate:sql];
        if (!res) {
            DLog(@"error to insert  search_record");
        } else {
            DLog(@"succ to insert  search_record");
        }
        [db close];
    }
}
- (NSMutableArray *)queryRecordList
{
    FMDatabase *db = [FMDatabase databaseWithPath:self.dbPath];
    if ([db open]) {
        NSMutableArray *recordArray = [[NSMutableArray alloc] init];
        NSString *sql = @"select * from search_record";
        
        FMResultSet *rs = [db executeQuery:sql];
        while ([rs next]) {
            NSString *recordString = [rs stringForColumn:@"record"];
            
            [recordArray addObject:recordString];
        }
        [db close];
        return recordArray;
    }
    return nil;
}
- (void)clearTheRecords
{
    FMDatabase *db = [FMDatabase databaseWithPath:self.dbPath];
    if ([db open]) {
        NSString *sql = @"delete from search_record";
        
        BOOL rs = [db executeUpdate:sql];
        
        if (!rs) {
            DLog(@"error to clear search_record");
        }else{
            DLog(@"success to clear search_record");
        }
        
        [db close];
    }
}
- (void)insertFavoriteWithInfo:(HomePageHouseModel *)homePageHouseModel
{
    FMDatabase *db = [FMDatabase databaseWithPath:self.dbPath];
    if ([db open])
    {
        NSString * sqlString = [NSString stringWithFormat:@"insert into favorite (house_id, long_price, middle_price,  short_price, price, landmark_name, img_url, title, bedroom_num, room_num, washroom_num, square, person_num, bed_num, min_nights, district_id, house_type,lat,lon,special_price_status,special_price) values('%@','%@','%@','%@','%@','%@','%@','%@','%@','%@','%@','%@','%@','%@','%@','%@','%@','%@','%@','%@','%@')",homePageHouseModel.houseId,homePageHouseModel.longPrice,homePageHouseModel.middlePrice,homePageHouseModel.shortPrice,homePageHouseModel.price,homePageHouseModel.landmarkName,homePageHouseModel.imgURLString,homePageHouseModel.title,homePageHouseModel.bedroomNum,homePageHouseModel.roomNum,homePageHouseModel.washroomNum,homePageHouseModel.square,homePageHouseModel.personNum,homePageHouseModel.bedNum,homePageHouseModel.minNights,homePageHouseModel.districtId,homePageHouseModel.houseType,homePageHouseModel.latitude,homePageHouseModel.longitude,[NSString stringWithFormat:@"%d",homePageHouseModel.specialPriceStatus],homePageHouseModel.specialPrice];
        
        BOOL res = [db executeUpdate:sqlString];
        
        if (!res)
        {
            DLog(@"error to insert favorite");
        } else {
            DLog(@"succ to insert favorite");
        }
        [db close];
    }
}
- (BOOL)queryHouseIsFavoritedWithHouseId:(NSString *)houseId
{
    FMDatabase *db = [FMDatabase databaseWithPath:self.dbPath];
    if ([db open])
    {
        NSString *sqlString = [NSString stringWithFormat:@"select count(*) from favorite where house_id = '%@'",houseId];
        
        FMResultSet *rs = [db executeQuery:sqlString];
        while ([rs next]) {
            NSInteger count = [rs intForColumn:@"count(*)"];
            
            [db close];
            
            if (count == 0) {
                return NO;
            }else{
                return YES;
            }
        }
    }
    return NO;
}
- (void)deleteFavoriteWithHouseId:(NSString *)houseId
{
    FMDatabase *db = [FMDatabase databaseWithPath:self.dbPath];
    if ([db open])
    {
        NSString *sqlString = [NSString stringWithFormat:@"delete from favorite where house_id = '%@'",houseId];
        
        BOOL rs = [db executeUpdate:sqlString];
        if (!rs) {
            DLog(@"error to delete favorite");
        }else{
            DLog(@"success to delete favorite");
        }
        
        [db close];
    }
}
- (NSMutableArray *)queryFavoriteItems
{
    FMDatabase *db = [FMDatabase databaseWithPath:self.dbPath];
    if ([db open]) {
        NSMutableArray *array = [[NSMutableArray alloc] init];
        NSString *sql = @"select * from favorite";
        
        FMResultSet *rs = [db executeQuery:sql];
        while ([rs next]) {
            HomePageHouseModel *homePageHouseModel = [[HomePageHouseModel alloc] init];
            homePageHouseModel.houseId = [rs stringForColumn:@"house_id"];
            homePageHouseModel.houseType = [rs stringForColumn:@"house_type"];
            homePageHouseModel.imgURLString = [rs stringForColumn:@"img_url"];
            homePageHouseModel.title = [rs stringForColumn:@"title"];
            homePageHouseModel.longPrice = [rs stringForColumn:@"long_price"];
            homePageHouseModel.price = [rs stringForColumn:@"price"];
            homePageHouseModel.bedroomNum = [rs stringForColumn:@"bedroom_num"];
            homePageHouseModel.roomNum = [rs stringForColumn:@"room_num"];
            homePageHouseModel.washroomNum = [rs stringForColumn:@"washroom_num"];
            homePageHouseModel.square = [rs stringForColumn:@"square"];
            homePageHouseModel.personNum = [rs stringForColumn:@"person_num"];
            homePageHouseModel.bedNum = [rs stringForColumn:@"bed_num"];
            homePageHouseModel.minNights = [rs stringForColumn:@"min_nights"];
            homePageHouseModel.middlePrice = [rs stringForColumn:@"middle_price"];
            homePageHouseModel.landmarkName = [rs stringForColumn:@"landmark_name"];
            homePageHouseModel.specialPriceStatus = [[rs stringForColumn:@"special_price_status"] boolValue];
            homePageHouseModel.specialPrice = [rs stringForColumn:@"special_price"];
            
            [array addObject:homePageHouseModel];
        }
        [db close];
        return array;
    }
    return nil;
}
@end
